const dbConfig = require('../dbConfig')
const mysql = require('mysql')

let getInfo = (sql) => {
    return new Promise((resolve, reject) => {
        try {
            let connection = mysql.createConnection(dbConfig)
            connection.connect()
            connection.query(sql, (err, result) => {
                connection.end()
                if (err) {
                    console.log('[SELECT ERROR]：', err.message)
                    reject({ 'errCode': '400', 'errMsg': 'fail' })
                }
                let str = JSON.parse(JSON.stringify(result))
                let obj = { 'errCode': '0', 'errMsg': 'success', 'data': str }
                resolve(obj)
            })

        } catch (e) {
            console.log(new Date() + 'getStdInfo出错,错误信息:' + e)
            reject({ 'errCode': '400', 'errMsg': 'fail' })
        }
    })
}

let sendRes = (req, res, data) => {
    res.send(data)
}

let GetInfo = {
    getStdInfo: async (req, res) => {
        let sql = 'SELECT Sno,Sname,Ssex,Sage,Sdept FROM student WHERE isShow=1'
        let obj = await getInfo(sql)
        sendRes(req, res, obj)
    },
    getCourseInfo: async (req, res) => {
        let sql = `SELECT Cno,Cname,Cpno,Ccredit FROM course WHERE isShow=1`
        let obj = await getInfo(sql)
        sendRes(req, res, obj)
    },
    getSCInfo: async (req, res) => {
        let sql = `SELECT Sname,Cname,Sno,Cno,Grade FROM
        (SELECT *
        FROM
        (SELECT * FROM student WHERE isShow=1) AS t1
        RIGHT JOIN 
        (SELECT * FROM SC WHERE isShow=1) AS t2 USING (Sno,isShow)) AS t3
        LEFT JOIN (SELECT * FROM course WHERE isShow=1) AS t4 USING (Cno)`
        let obj = await getInfo(sql)
        sendRes(req, res, obj)
    }
}

module.exports = GetInfo